<%@ page import="com.nextStep.entity.Users" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>

<html lang="en" dir="ltr">
<head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />

    <title>mySqlExecute【sqlplus执行】</title>

    <!-- GOOGLE FONTS -->
    <link href="https://fonts.googleapis.com/css?family=Montserrat:400,500|Poppins:400,500,600,700|Roboto:400,500" rel="stylesheet"/>
    <link href="https://cdn.materialdesignicons.com/3.0.39/css/materialdesignicons.min.css" rel="stylesheet" />

    <!-- PLUGINS CSS STYLE -->
    <link href="../plugins/toaster/toastr.min.css" rel="stylesheet" />
    <link href="../plugins/nprogress/nprogress.css" rel="stylesheet" />
    <link href="../plugins/flag-icons/css/flag-icon.min.css" rel="stylesheet"/>
    <link href="../plugins/jvectormap/jquery-jvectormap-2.0.3.css" rel="stylesheet" />
    <link href="../plugins/ladda/ladda.min.css" rel="stylesheet" />
    <link href="../plugins/select2/css/select2.min.css" rel="stylesheet" />
    <link href="../plugins/daterangepicker/daterangepicker.css" rel="stylesheet" />

    <!-- SLEEK CSS -->
    <link id="sleek-css" rel="stylesheet" href="../css/sleek.css" />



    <!-- FAVICON -->
    <link href="../img/favicon.png" rel="shortcut icon" />

    <!--
      HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries
    -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
    <script src="../plugins/nprogress/nprogress.js"></script>
</head>

<%--class="sidebar-fixed sidebar-dark header-light header-fixed"--%>
<body  id="body" >
<script>   //加载进度条
    NProgress.configure({ showSpinner: false });
    NProgress.start();
</script>

<%--判断是否登录--%>
<%
    if(session.getAttribute("LOGIN_DATA")==null){
        response.sendRedirect("../login.jsp");
        return ;
    }
    Map map = (Map) session.getAttribute("DATABASE_DATA");
%>


<div class="mobile-sticky-body-overlay"></div>

<div class="wrapper">



    <div class="page-wrapper">
        <!-- Header -->
        <header class="main-header " id="header">
            <nav class="navbar navbar-static-top navbar-expand-lg">
                <!-- Sidebar toggle button -->
                <%--<button id="sidebar-toggler" class="sidebar-toggle">--%>
                    <span class="sr-only">Toggle navigation</span>
                <%--</button>--%>
                <!-- search form -->
                <div class="search-form d-none d-lg-inline-block">
                    <div class="input-group">
                        <button type="button" name="search" id="search-btn" class="btn btn-flat">
                            <i class="mdi mdi-magnify"></i>
                        </button>
                        <input type="text" name="query" id="search-input" class="form-control" value="mysqlExecute sql执行"
                               autofocus autocomplete="off"  readonly="readonly" style="font-size: 28px"/>
                    </div>
                    <div id="search-results-container">
                        <ul id="search-results"></ul>
                    </div>
                </div>

                <div class="navbar-right ">
                    <ul class="nav navbar-nav">
                        <!-- 菜单栏 -->
                        <li class="dropdown user-menu">
                            <button href="#" class="dropdown-toggle nav-link" data-toggle="dropdown">
                                <img src="../img/user/user.png" class="user-image" alt="User Image" />
                                <span class="d-none d-lg-inline-block"><%=((Users)session.getAttribute("LOGIN_DATA")).getName() %></span>
                            </button>
                            <ul class="dropdown-menu dropdown-menu-right">
                                <!-- User image -->
                                <li class="dropdown-header">
                                    <img src="../img/user/user.png" class="img-circle" alt="User Image" />
                                    <div class="d-inline-block">
                                        <%=((Users)session.getAttribute("LOGIN_DATA")).getName()%> <small class="pt-1"><%=((Users)session.getAttribute("LOGIN_DATA")).getEmail()%></small>
                                    </div>
                                </li>
                                <li>
                                    <a href="index"><i class="mdi mdi-account"></i> 主页</a>
                                </li>
                                <li>
                                    <a href="dbConfigAdmin"> <i class="mdi mdi-settings"></i> 数据库配置 </a>
                                </li>
                                <li>
                                    <a href="sqlplus"> <i class="mdi mdi-settings"></i> sqlPlus 命令操作</a>
                                </li>
                                <li class="dropdown-footer">
                                    <a href="../user/exit"> <i class="mdi mdi-logout"></i> 退出 </a>
                                </li>
                            </ul>
                        </li>
                    </ul>
                </div>

            </nav>


        </header>

        <%--数据展示--%>
        <div class="content-wrapper">
            <div class="card-header justify-content-between align-items-center card-header-border-bottom">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <font size="5" >当前数据库:</font>

                <select id="selectDataBase" class="form-control input-sm" style="width:200px; display: inline;" onchange="selDbChange()">
                    <%
                        for(Object keySel:map.keySet()){
                            String dataBaseNameSel = (String)keySel;  //数据库名
                    %>
                    <option id ="<%=dataBaseNameSel%>" value ="<%=dataBaseNameSel%>"><%=dataBaseNameSel%></option>
                    <%
                        }
                    %>
                </select>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <font size="5" >参考信息</font><font size="2" >查询表名或字段名</font>
                &nbsp;&nbsp;&nbsp;
                <font size="5" >表：</font>
                <select id="selectTable" class="form-control input-sm" style="width:200px; display: inline;" onchange="selTbChange()">

                </select>
                &nbsp;
                <font size="5" >列名：</font>
                <select id="selectColumns" class="form-control input-sm" style="width:200px; display: inline;">

                </select>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <button class="btn btn-primary " onclick="javascript :history.back(-1)" >返回上一层</button>





            </div>

            <div class="content">
                <div class="form-group">
                    <label ><b>sql命令</b></label>
                    <textarea class="form-control" id="sqlplus" rows="5"></textarea>
                    <br>
                    <button class="btn btn-primary btn-pill"  onclick="execSql()">执行选中内容 </button>
                </div>

                <div class="form-group">
                    <label ><b>返回结果</b></label>
                    <%--<textarea class="form-control" id="exampleFormContrdolTextarea1" rows="5"></textarea>--%>
                    <div style="overflow-x:scroll;">
                        <table id="table" style="color: #0b0e19" class="table table-bordered">
                        </table>
                    </div>

                </div>


            </div>
        </div>

        <jsp:include page="footer.jsp"/>

    </div>
</div>



<script src="../plugins/jquery/jquery.min.js"></script>
<script src="../plugins/bootstrap/js/bootstrap.bundle.min.js"></script>
<script src="../plugins/toaster/toastr.min.js"></script>
<script src="../plugins/slimscrollbar/jquery.slimscroll.min.js"></script>
<script src="../plugins/charts/Chart.min.js"></script>
<script src="../plugins/ladda/spin.min.js"></script>
<script src="../plugins/ladda/ladda.min.js"></script>
<script src="../plugins/jquery-mask-input/jquery.mask.min.js"></script>
<script src="../plugins/select2/js/select2.min.js"></script>
<script src="../plugins/jvectormap/jquery-jvectormap-2.0.3.min.js"></script>
<script src="../plugins/jvectormap/jquery-jvectormap-world-mill.js"></script>
<script src="../plugins/daterangepicker/moment.min.js"></script>
<script src="../plugins/daterangepicker/daterangepicker.js"></script>
<script src="../plugins/jekyll-search.min.js"></script>
<script src="../js/sleek.js"></script>
<script src="../js/chart.js"></script>
<script src="../js/date-range.js"></script>
<script src="../js/map.js"></script>
<script src="../js/custom.js"></script>

<script type="text/javascript">

    function selDbChange() {
        //alert(document.getElementById("selectDataBase").value)
        var dbName=document.getElementById("selectDataBase").value;
        $.ajax({
            url: "showTable",
            type: "post",
            data: {dbName: dbName},
            success: function (data) {
                //更新数据表
                var selectTable = document.getElementById("selectTable");
                selectTable.options.length = 0;
                for (var i = 0; i < data.length; i++) {
                    selectTable.add(new Option(data[i], data[i]));  //添加当前查询数据库的表名
                }
            }
        })
    }

    function selTbChange() {
        //alert(document.getElementById("selectTable").value)
        var dbName=document.getElementById("selectDataBase").value;
        var tbName=document.getElementById("selectTable").value;
        $.ajax({
            url: "selectColumns",
            type: "post",
            data: {dbName:dbName,tbName:tbName},
            success: function (data) {
                //更新数据表
                var selectColumns = document.getElementById("selectColumns");
                selectColumns.options.length = 0;
                for (var i = 0; i < data.length; i++) {
                    selectColumns.add(new Option(data[i], data[i]));  //添加当前查询数据库的表名
                }
            }
        })
    }


    function execSql() {
        var dbName = document.getElementById("selectDataBase").value;  //数据库下拉框value
        var sqlplus = document.getElementById("sqlplus")
        var start = sqlplus.selectionStart;
        var end = sqlplus.selectionEnd;
        var table=document.getElementById("table");  //获取Table
        if(start == end  ){
            alert("当前未选中内容,使用鼠标选中要执行的sql，后在点击执行")
            var length= table.rows.length;          //获得Table下的行数
            if(length!=0){              //如果有行，则清空
                for(var i=length-1;i>=0;i--){
                    table.deleteRow(i);
                }
            }
            return ;
        }

        //alert(sqlplus.value.substring(start,end))
        var sql = sqlplus.value.substring(start,end);
        if(sql.indexOf("use ") != -1){
            var inputDbName = sql.substring(sql.indexOf("use ")+4 ,sql.length).trim();  //获得输入的数据库名
            var opts = document.getElementById("selectDataBase").options;   //获得数据库下拉框对象
            var i =0;
            for(i=0;i<opts.length;i++){   //遍历数据库下拉框 匹配输入的数据库名
                if(opts[i].value == inputDbName){  //匹配成功
                    document.getElementById("selectDataBase").value =inputDbName;
                    break;
                }
            }
            if(opts.length == i){   //匹配失败
                alert("输入的数据库名有误，核对数据库名称后重试");
            }
            //alert(document.getElementById("selectDataBase").value = sql.substring(sql.indexOf("use ")+4 ,sql.length).trim())
        }else{

            var length= table.rows.length;          //获得Table下的行数
            if(length!=0){              //如果有行，则清空
                for(var i=length-1;i>=0;i--){
                    table.deleteRow(i);
                }
            }
            $.ajax({
                url: "runSql",
                type: "post",
                data: {dbName:dbName,sql:sql},
                success: function (data) {

                    for (var i = 0; i < data.length; i++) {
                        var tr = table.insertRow();  //创建一行对象
                        for (var j = 0; j < data[i].length; j++) {
                            var  td =tr.insertCell();  //创建一列对象
                            if(i==0){
                                td.innerHTML="<b><font color='#4c84ff'>"+data[i][j]+"</font></b>";
                            }else{
                                td.innerHTML=data[i][j];
                            }
                        }
                        table.appendChild(tr);
                    }
                    document.getElementById('table').appendChild(table);
                }
            });
        }
    }


</script>



</body>
</html>
